package cn.edu.ctbu.web.mapper;
import cn.edu.ctbu.web.dao.AdminCourse;
import cn.edu.ctbu.web.dao.StudentCourse;
import cn.edu.ctbu.web.dao.TeacherCourse;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Pageable;

import java.util.List;

@Mapper
public interface CourseMapper {
    @Select("SELECT \n" +
            "    c.no AS no,\n" +
            "    c.name AS name,\n" +
            "    t.name AS teacher,\n" +
            "    c.credit\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "JOIN \n" +
            "    tb_teacher t ON g.tno = t.no\n" +
            "WHERE \n" +
            "    s.no =  #{no} \n" +
            "ORDER BY \n" +
            "    c.no;")
    // 使用学号查询选课信息
    List<StudentCourse> findCourseByNoStudent(@Param("no") String no, Pageable pageable);

    @Select("SELECT COUNT(*) FROM tb_grade g " +
            "JOIN tb_student s ON g.sno = s.no " +
            "WHERE s.no = #{no}")
    // 获取选课信息计数
    long countCoursesByNoStudent(@Param("no") String no);

    @Select("SELECT \n" +
            "    c.no AS no,\n" +
            "    c.name AS name,\n" +
            "    c.credit AS credit,\n" +
            "    COUNT(DISTINCT g.sno) AS count\n" +
            "FROM \n" +
            "    tb_course c\n" +
            "LEFT JOIN \n" +
            "    tb_grade g ON c.no = g.cno\n" +
            "WHERE \n" +
            "    c.tno = #{no}  -- 将'T20240001'替换为实际的教师编号\n" +
            "GROUP BY \n" +
            "    c.no, c.name, c.credit\n" +
            "ORDER BY \n" +
            "    c.no;")
    // 使用教师编号查询授课课程信息
    List<TeacherCourse> findCourseByNoTeacher(@Param("no") String no, Pageable pageable);


    @Select("SELECT COUNT(DISTINCT c.no) AS course_count\n" +
            "FROM \n" +
            "    tb_course c\n" +
            "JOIN \n" +
            "    tb_teacher t ON c.tno = t.no\n" +
            "WHERE \n" +
            "    t.no = #{no};")
    // 获取授课课程信息计数
    long countCourseByNoTeacher(@Param("no") String no);

    @Select("SELECT \n" +
            "    c.id AS course_id,\n" +
            "    c.no AS course_no,\n" +
            "    c.name AS course_name,\n" +
            "    t.no AS teacher_no,\n" +
            "    t.name AS teacher_name,\n" +
            "    c.credit AS course_credit\n" +
            "FROM \n" +
            "    tb_course c\n" +
            "JOIN \n" +
            "    tb_teacher t ON c.tno = t.no\n" +
            "ORDER BY \n" +
            "    c.id;")
    List<AdminCourse> findAllCourse(Pageable pageable);

    @Select("SELECT \n" +
            "    COUNT(*) AS total_courses\n" +
            "FROM \n" +
            "    tb_course c")
    Long countAllCourse();


    @Select("SELECT \n" +
            "    c.no AS no,\n" +
            "    c.name AS name,\n" +
            "    c.credit AS credit,\n" +
            "    COUNT(DISTINCT g.sno) AS count\n" +
            "FROM \n" +
            "    tb_course c\n" +
            "LEFT JOIN \n" +
            "    tb_grade g ON c.no = g.cno\n" +
            "WHERE \n" +
            "    c.tno = #{tno} \n" +
            "GROUP BY \n" +
            "    c.no, c.name, c.credit\n" +
            "ORDER BY \n" +
            "    c.no;")
    // 使用教师编号查询授课课程信息
    List<TeacherCourse> findCourseByTno(@Param("tno") String tno);


    /**
     * Admin端课程表 增、删、改、查
     * {操作名}{操作对象}{使用层}
     **/

    @Insert("INSERT INTO tb_course (no, name, tno, credit)\n" +
            "VALUES (#{cno}, #{cname}, #{tno}, #{credit})\n")
    // 新增课程表信息
    void insertCourseAdmin(@Param("cno") String cno, @Param("cname") String cname,
                           @Param("tno") String tno, @Param("credit") int credit);

    @Delete("DELETE FROM tb_course\n" +
            "WHERE id = #{id};")
    // 删除课程表信息
    void deleteCourseAdmin(@Param("id") String id);

    @Update("UPDATE tb_course\n" +
            "SET \n" +
            "    no = #{cno},\n" +
            "    name = #{cname},\n" +
            "    tno = #{tno},\n" +
            "    credit = #{credit}\n" +
            "WHERE \n" +
            "    id = #{id};")
    // 更新课程表信息
    void updateCourseAdmin(@Param("cno") String cno, @Param("cname") String cname,
                           @Param("tno") String tno, @Param("credit") int credit,
                           @Param("id") int id);

    @Select("SELECT \n" +
            "    c.id AS course_id,\n"+
            "    c.no AS course_no,\n" +
            "    c.name AS course_name,\n" +
            "    c.tno AS teacher_no,\n" +
            "    t.name AS teacher_name,\n" +
            "    c.credit AS course_credit\n" +
            "FROM \n" +
            "    tb_course c\n" +
            "JOIN \n" +
            "    tb_teacher t ON c.tno = t.no\n" +
            "WHERE \n" +
            "    c.name LIKE CONCAT('%', #{cname}, '%')\n" +
            "ORDER BY \n" +
            "    c.no;")
    // 查询课程表信息
    List<AdminCourse> searchCourseAdmin(@Param("cname") String cname, Pageable pageable);

    @Select("SELECT \n" +
            "    COUNT(*) AS course_count\n" +
            "FROM \n" +
            "    tb_course\n" +
            "WHERE \n" +
            "    name LIKE CONCAT('%', #{cname}, '%');")
    // 查询课程表信息（计数）
    Long countSearchCourseAdmin(@Param("cname") String cname);
}
